﻿CREATE TRIGGER [dbo].[tr_CarInfo_VIN]
ON dbo.t_CarInfo 
FOR INSERT, UPDATE
AS


DECLARE @RC int SET @RC = @@ROWCOUNT IF @RC = 0 RETURN
IF NOT UPDATE (VIN) RETURN
IF NOT EXISTS(SELECT * FROM inserted WHERE NOT VIN IS NULL)RETURN
IF @RC > 1 BEGIN ROLLBACK TRAN RAISERROR('Multiple VINs update/insert is not supported',16,1) RETURN END

IF EXISTS(SELECT * FROM inserted i INNER JOIN deleted d ON i.VIN = d.VIN)RETURN

DECLARE @LoanID varchar(50),@VIN sysname,@TrackerRequestID int,@Dealer sysname,@ErrMsg nvarchar(max)

SELECT @TrackerRequestID = tr.TrackerRequestID, @VIN = tr.VIN, @Dealer = d.Name
FROM deleted AS i
INNER JOIN t_Loan AS l ON i.LoanID = l.ID
INNER JOIN t_Dealer_SalesPersons dsp ON l.DealerSalesPersonID = dsp.ID
INNER JOIN v_TrackerRequest AS tr ON tr.VIN = i.VIN 
INNER JOIN t_Dealer d ON tr.DealerID = d.ID
INNER JOIN inserted ON i.LoanID = inserted.LoanID
WHERE     (tr.IsActive = 1)AND tr.DealerID = dsp.DealerID AND inserted.VIN > ''

IF @TrackerRequestID > 0 BEGIN
	ROLLBACK TRAN
	RAISERROR('Active Tracker Request [%d] exists for this VIN [%s] from dealer [%s]',16,1,@TrackerRequestID,@VIN,@Dealer)
	RETURN
END
-- Check if VIN already belongs to loan -------------------------------------------------------------
INSERT INTO v_Borrower_CheckVIN
SELECT L_B.BorrowerID,i.LoanID
FROM inserted AS i
INNER JOIN t_Loan_Borrower L_B ON i.LoanID = L_B.LoanID

--SELECT @LoanID = ci.LoanID,@VIN = ci.VIN
--FROM t_CarInfo AS ci
--INNER JOIN inserted AS i ON i.VIN = ci.VIN
--INNER JOIN t_Loan_Borrower L_B ON i.LoanID = L_B.LoanID
--INNER JOIN t_Borrower B ON L_B.BorrowerID = B.ID
--INNER JOIN t_Loan_Borrower L_B_1 ON i.LoanID <> L_B_1.LoanID
--INNER JOIN t_Borrower B_1 ON L_B_1.BorrowerID = B.ID AND B.SSN = B_1.SSN

--IF @LoanID > '' BEGIN ROLLBACK TRAN	RAISERROR('VIN:<B onclick="showApplInfo(''%s'',''&pageID=10'')" style="cursor:hand" title="Show Loan Info">%s</B> is already used in LoanID:<B onclick="showApplInfo(''%s'')" style="cursor:hand" title="Show Loan Info">%s</B>',16,1,@LoanID,@VIN,@LoanID,@LoanID)RETURN END
-----------------------------------------------------------------------------------------------------
-- Update/Insert t_Car
IF NOT EXISTS(SELECT * FROM inserted i INNER JOIN t_Car c ON i.VIN = c.VIN)
	IF EXISTS(SELECT * FROM inserted i CROSS JOIN deleted d WHERE dbo.VIN_Compare(i.VIN,d.VIN) = 1 OR dbo.VinIsID(d.VIN) = 1)
		UPDATE t_Car SET VIN = i.VIN FROM t_Car c
		INNER JOIN deleted d ON c.VIN = d.VIN
		CROSS JOIN inserted i
	ELSE
		INSERT INTO t_Car(VIN) SELECT i.VIN FROM inserted i LEFT JOIN t_Car c ON i.VIN = c.VIN WHERE i.VIN > '' AND c.VIN IS NULL
--ROLLBACK TRAN
--RAISERROR('ERROR!',16,1)
